package com.jse.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.jse.Jse;
import com.jse.cache.Cache;
import com.jse.cache.impl.FIFOCache;
import com.jse.entity.Record;
import com.jse.log.Log;
import com.jse.log.Logs;
import com.jse.util.JseMap;
import com.jse.util.Strings;

public class Dao {

	DataSource dataSource;
	Connection conn=null;
	PreparedStatement pst=null;
	private ResultSet rs=null;
	private Cache cache;
	Log log=Logs.get();

	public Cache getCache() {
		return cache;
	}
	public void setCache(Cache cache) {
		this.cache = cache;
	}
	public Dao(DataSource dataSource) {
		this.dataSource=dataSource;
		if(cache==null) {
			cache=new FIFOCache<>(1000);
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Record> query(String tableName){
		return query(tableName,null);
	}
	public List<Record> query(String tableName,Cnd cnd){
		return query(tableName,cnd,null);
	}
	@SuppressWarnings("unchecked")
	public List<Record> query(String tableName,Cnd cnd,Pager pager){
		List<Record> list=null;
		String sql="select * from "+tableName;
		if(cnd==null&&pager==null) {
			if(cache.containsKey("sqllist:"+tableName)) {
				list=(List<Record>) cache.get("sqllist:"+tableName);
			}else {
				list=execute(sql, null);
				cache.put("sqllist:"+tableName, list);
			}
			return list;
		}else {
			if(Jse.DBTYPE.equals("oracle")&&pager!=null) {
				String sqltemp=String.format("SELECT * FROM (SELECT Z.*, ROWNUM RN FROM (" +
						sql+cnd+
						") Z WHERE ROWNUM <= %d) WHERE RN > %d",pager.getOffset() + pager.getSize(),
                        pager.getOffset());
				return list=execute(sqltemp, null);
			}else if(Jse.DBTYPE.equals("hsqldb")&&pager!=null) {
				return list=execute(sql+cnd+String.format(" LIMIT %d OFFSET %d", pager.getSize(),
                        pager.getOffset()), null);
			}else if(Jse.DBTYPE.equals("psql")&&pager!=null) {
				return list=execute(sql+cnd+String.format(" LIMIT %d OFFSET %d", pager.getSize(),
                        pager.getOffset()), null);
			}else if(Jse.DBTYPE.equals("server2005")&&pager!=null) {
	            String xSql = sql.trim().substring(6)+cnd;
	            String pre = String.format(    "select * from(select row_number()over(order by __tc__)__rn__,* from(select top %d 0 __tc__, ",
	                                                            pager.getOffset() + pager.getSize());
	            String last = String.format(")t)tt where __rn__ > %d", pager.getOffset());
	            sql=(pre + xSql + last);
			}else if(Jse.DBTYPE.equals("server2012")&&pager!=null) {
	            sql=sql+cnd+String.format(" OFFSET %d ROWS FETCH NEXT %d ROW ONLY", pager.getOffset(), pager.getSize());
			}else if(Jse.DBTYPE.equals("derby")&&pager!=null) {
	            sql=sql+cnd+String.format(" OFFSET %d ROWS FETCH NEXT %d ROW ONLY", pager.getOffset(), pager.getSize());
			}else if(Jse.DBTYPE.equals("db2")&&pager!=null) {
				String pre = "SELECT * FROM (SELECT ROW_NUMBER() OVER() AS ROWNUM, T.* FROM (";
	            String last = String.format(    ") T) AS A WHERE ROWNUM BETWEEN %d AND %d",
	                    pager.getOffset() + 1,
	                    pager.getOffset() + pager.getSize());
	            sql=(pre + sql+cnd + last);
			}
			return list=execute(sql+cnd+(pager==null?"":pager.toSql()), null);
		}
	}
	
	public int update(String tableName,JseMap chain,Cnd cnd) {
		StringBuffer sql=new StringBuffer("update %s set ");
		List<Object> list=new LinkedList<>();
		chain.forEach((k,v)->{
			sql.append(k).append("=").append("%s").append(",");
			list.add(v);
		});
		sql.deleteCharAt(sql.length()-1);
		return execUpdate(sql.toString(), list.toArray());
	}
	public JseMap pager(String tableName,Cnd cnd,int num,int size) {
		Pager pager=new Pager(num, size);
		List<Record> list=query(tableName, cnd, pager);
		JseMap mapper=new JseMap();
		mapper.put("list", list);
		int count=count(tableName,cnd);
		pager.setCount(count);
		mapper.put("pager",pager);
		return mapper;
	}
	public JseMap pager(String tableName,Cnd cnd,Pager pager) {
		List<Record> list=query(tableName, cnd, pager);
		JseMap mapper=new JseMap();
		mapper.put("list", list);
		int count=count(tableName,cnd);
		pager.setCount(count);
		mapper.put("pager",pager);
		return mapper;
	}
	public Record fetch(String tableName,Cnd cnd) {
		List<Record> list=query(tableName, cnd);
		return list!=null&&list.size()>0?list.get(0):null;
	}
	public int count(String tableName,Object cnd) {
		if(cnd==null)
			cnd="";
		String sql="select count(*) from "+tableName+" "+cnd;
        this.createStatement(sql);  
        try {  
            rs=pst.executeQuery();
            while (rs.next())
            	return rs.getInt(1);
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
            this.close();  
        }  
        return 0;
	}
	public List<Record> sql(String sql) {
	    return execute(sql);
	}
	
	public List<Record> select(String sql){
		return sql(sql);
	}
	public List<Record> select(String sql,Object...param) {
		return execute(sql);
	}
	
	@SuppressWarnings("unchecked")
	public int delete(String table,Object cnd){
		StringBuffer sql = new StringBuffer("delete from ").append(table);
		if(!Strings.isEmpty(cnd)) {
			if(cnd instanceof Cnd)
				sql.append(cnd);
			else if(cnd instanceof String) {
				String str=cnd.toString();
				sql.append(Cnd.wrap(str));
			}
			else if(cnd instanceof Integer) {
				sql.append(Cnd.where("id", "=", cnd));
			}else if(cnd.getClass().isArray()) {
				sql.append(Cnd.where("id", "in", cnd));
			}else if(cnd instanceof List) {
				sql.append(Cnd.where("id", "in", cnd));
			}else if(cnd instanceof Map) {
				Map<String, Object> mp=(Map<String, Object>) cnd;
				Cnd cn=Cnd.where("1", "=", 1);
				mp.forEach((k,v)->{
					sql.append(cn.and(k,"=",v));
				});
			}else {
				return -1;
			}
			return execUpdate(sql.toString(),null);
		}else {
			return -1;
		}
	}
	public List<Record> execute(String sql){
		return execute(sql, null);
	}
	public List<Record> execute(String sql,Object...args){  
		List<Record> list=new LinkedList<Record>();
        this.createStatement(sql);  
        try {  
            for(int i=0;args!=null && i<args.length;i++){  
                pst.setObject(i+1, args[i]);  
            }
            rs=pst.executeQuery();  
            while (rs.next())
            	list.add(Record.create(rs));
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
            this.close();  
        }  
        return list;
    }  
	/**
	 * 更新删除
	 * @param sql
	 * @param args
	 * @return
	 */
	public int execUpdate(String sql,Object...args) {
		int rowNum=0;  
        this.createStatement(sql);  
        try {  
            for(int i=0;args!=null && i<args.length;i++){  
                pst.setObject(i+1, args[i]);  
            }  
            rowNum=pst.executeUpdate();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }finally{  
            this.close();  
        }  
        return rowNum;  
	}
	
	public Connection getConn() throws SQLException {
		return dataSource.getConnection();
	}
	
	private void createStatement(String sql){  
        try {  
            pst=this.getConn().prepareStatement(sql);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
	
	public void close(){  
        try {  
            if(rs!=null){  
                rs.close();  
            }  
            if(pst!=null){  
                pst.close();  
            }  
            if(conn!=null){  
                conn.close();  
            }  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
            rs=null;
            pst=null;
            conn=null;
        }  
    }
	public void insert(HashMap tbl) {
		// TODO Auto-generated method stub
		
	}  
	
}
